package com.free.mysql.mysql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.free.freemarker.core.SqlBuilder;
import com.free.freemarker.constant.SqlConstant;
import com.free.mysql.model.Column;
import com.free.mysql.model.Idx;
import com.free.mysql.model.Table;
import com.free.mysql.util.Utils;

public class MySqlBuilder implements SqlBuilder {

    public Connection createConnection() throws Exception {
        Class.forName(SqlConstant.SQL_DRIVER).newInstance();
        Connection con = DriverManager.getConnection(SqlConstant.SQL_URL, SqlConstant.USER, SqlConstant.PASSWORD);
        return con;
    }


    public Table getMetaData(String tableName, Connection con)
            throws SQLException {
        DatabaseMetaData dbmd = con.getMetaData();
        // ResultSet tableRS = dbmd.getTables(null, "%", "",new String[] { "TABLE" });
        // /*
        // *
        // 其中"%"就是表示*的意思，也就是任意所有的意思。其中m_TableName就是要获取的数据表的名字，如果想获取所有的表的名字，就可以使用"%"
        // * 来作为参数了。
        // */
        // while (tableRS.next()) {
        // String dbTableName = tableRS.getString("TABLE_NAME");
        // System.out.println("dbTableName:" + dbTableName);
        // }

        Table t = new Table();

        /**********************************获取表信息**************************************************/
        t.setTableName(tableName);

        // table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
        String[] types = {"TABLE"};
        ResultSet tableRS = dbmd.getTables(null, "%", tableName, types);
        while (tableRS.next()) {
            tableName = tableRS.getString("TABLE_NAME");
            if (tableName.equals(tableName)) {
                t.setRemarks(tableRS.getString("REMARKS"));
            }
        }

        /************************************************获取列信息********************************************************/
        List<Column> columnList = new ArrayList<>();
        ResultSet colRS = dbmd.getColumns(null, "%", tableName, "%");
        ResultSet rs = dbmd.getPrimaryKeys(null, null, tableName);
        String pk = null;
        while (rs.next()) {
            pk = rs.getString("COLUMN_NAME");
        }
        Column column;
        String columnName;
        String columnType;
        while (colRS.next()) {
            column = new Column();
            columnName = colRS.getString("COLUMN_NAME");
            columnType = colRS.getString("TYPE_NAME");
            if (columnName.equals(pk)) {
                column.setIsPK(1);

                if ("BIGINT".equalsIgnoreCase(columnType)) {
                    column.setDb_fieldName("BIGINT");
                    column.setFieldType("java.lang.String");
                    column.setSimpleFieldType("String");

                } else {
                    //默认 int  or integer
                    column.setDb_fieldName("INTEGER");
                    column.setFieldType("java.lang.Integer");
                    column.setSimpleFieldType("Integer");
                }

            } else {
                column.setIsPK(-1);
                /**
                 * mybatis 没有  datetime  映射文件只能是  Date 或  datestamp
                 */
                if ("DATE".equalsIgnoreCase(columnType) || "DATETIME".equalsIgnoreCase(columnType)) {
                    column.setDb_fieldName("DATE");
                    column.setIsTime(1);
                } else if ("TIMESTAMP".equalsIgnoreCase(columnType)) {
                    column.setDb_fieldName(columnType);
                    column.setIsTime(1);
                } else if ("TEXT".equalsIgnoreCase(columnType)) {
                    column.setDb_fieldName("VARCHAR");
                } else if ("INT".equalsIgnoreCase(columnType)) {
                    column.setDb_fieldName("INTEGER");
                } else {
                    column.setDb_fieldName(columnType);
                }
                String fieldType = Utils.javaType(columnType, column);
                column.setFieldType(fieldType);
                column.setSimpleFieldType(fieldType.substring(fieldType.lastIndexOf(".") + 1));
            }
            column.setDb_column(columnName);
            columnName = Utils.colName(columnName);
            column.setColumnName(columnName);
            column.setColumnFirstUppder(Utils.firstUpper(columnName));
            String remarksDescription = colRS.getString("REMARKS");
            int left;
            if ((left = remarksDescription.indexOf("(")) > 0) {
                String remarks = remarksDescription.substring(0, left);
                column.setRemarks(remarks);
                column.setRemarksDescription(remarksDescription);
            } else {
                column.setRemarks(remarksDescription);
                column.setRemarksDescription("");
            }

            column.setIsNullable(colRS.getInt("NULLABLE"));
            columnList.add(column);
        }
        t.setColumnList(columnList);

        /***************************获取索引的信息***************************/


        ResultSet rsIdx = dbmd.getIndexInfo(null, null, tableName, false, true);
        if (rsIdx != null) {
            List<Idx> idxList = new ArrayList<>();
            Idx idx;
            int index;
            List<Column> colList;
            List<Column> columnsList = t.getColumnList();
            while (rsIdx.next()) {
                String idxName = rsIdx.getString("INDEX_NAME");  //索引名字
                String idxColumnName = rsIdx.getString("COLUMN_NAME");//列名
                String type = rsIdx.getString("TYPE");

                index = filter(idxList, idxName);
                if (index >= 0) {
                    idx = idxList.get(index);
                    colList = idx.getColList();
                    column = filterColumn(columnsList, idxColumnName);
                    if (column != null) {
                        colList.add(column);
                    }

                } else {
                    idx = new Idx();
                    idx.setName(idxName);
                    colList = new ArrayList<>();
                    column = filterColumn(columnsList, idxColumnName);
                    colList.add(column);
                    idx.setColList(colList);
                    idx.setType(type);
                    idxList.add(idx);
                }
            }
            t.setIdxList(idxList);
        }
        return t;
    }

    /**
     * 筛选符合条件的Idx信息
     *
     * @param idxList
     * @param idxName
     * @return
     * @author liuhuan
     */
    private int filter(List<Idx> idxList, String idxName) {
        if (idxList != null) {
            Idx idx;
            for (int i = 0, len = idxList.size(); i < len; i++) {
                idx = idxList.get(i);
                if (idxName.equals(idx.getName())) {
                    return i;
                }
            }
        }
        return -1;
    }

    /**
     * 过滤列
     *
     * @param colList
     * @param dbColumn
     * @return
     * @author liuhuan
     */
    private Column filterColumn(List<Column> colList, String dbColumn) {
        if (colList != null) {
            Column col;
            for (int i = 0, len = colList.size(); i < len; i++) {
                col = colList.get(i);
                if (dbColumn.equals(col.getDb_column())) {
                    return col;
                }
            }
        }
        return null;
    }
}
